package com.migration.model.migration.scripts;

import com.migration.model.migration.AbstractMigrationScript;
import com.migration.model.migration.CustomExecutableMigrationScript;
import com.migration.model.newdb.RiskProtection;
import com.migration.model.newdb.RiskProtectionAlertSetting;
import com.migration.model.olddb.RaDefendMeasureSetting;
import com.migration.model.olddb.RaDefendMeasures;
import com.migration.util.CommonUtils;
import com.migration.util.JsonUtils;
import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import jakarta.persistence.TypedQuery;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Predicate;
import jakarta.persistence.criteria.Root;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 设备-保护措施表(risk_protection)迁移脚本
 */
@Component
public class A013_RiskProtectionMigrationScript extends AbstractMigrationScript implements CustomExecutableMigrationScript {

    @Autowired
    @Qualifier("oldEntityManagerFactory")
    private EntityManagerFactory oldEntityManagerFactory;

    @Autowired
    @Qualifier("newEntityManagerFactory")
    private EntityManagerFactory newEntityManagerFactory;

    /**
     * 构造函数，初始化迁移配置
     */
    public A013_RiskProtectionMigrationScript() {
        super(
            "risk_protection",
            "设备-保护措施数据迁移",
            "ra_defend_measures",
            "risk_protection",
            13
        );
    }

    /**
     * 执行数据迁移，在内存中组装数据，然后插入目标表
     * 这个方法会在MigrationScriptServiceImpl中被调用
     *
     * @param dataSource 数据源
     * @return 影响的行数
     */
    @Override
    public int executeCustomMigration(DataSource dataSource) {
        // 先清空目标表
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.execute(getTruncateSql());
        jdbcTemplate.execute("TRUNCATE TABLE risk_protection_alert_setting;");

        // 调用实体类处理方法
        return executeWithEntityProcessing(oldEntityManagerFactory, newEntityManagerFactory);
    }

    /**
     * 使用JPA实体类在内存中组装数据，然后插入目标表
     * 内部实现方法，不直接暴露给外部调用
     *
     * @param oldEntityManagerFactory 源数据库的EntityManagerFactory
     * @param newEntityManagerFactory 目标数据库的EntityManagerFactory
     * @return 处理的数据行数
     */
    protected int executeWithEntityProcessing(EntityManagerFactory oldEntityManagerFactory, EntityManagerFactory newEntityManagerFactory) {
        EntityManager oldEntityManager = oldEntityManagerFactory.createEntityManager();
        EntityManager newEntityManager = newEntityManagerFactory.createEntityManager();
        int rowsProcessed = 0;

        try {
            // 开始事务
            oldEntityManager.getTransaction().begin();
            newEntityManager.getTransaction().begin();

            // 第一步：查询所有RaDefendMeasures数据
            CriteriaBuilder cb = oldEntityManager.getCriteriaBuilder();
            CriteriaQuery<RaDefendMeasures> cq = cb.createQuery(RaDefendMeasures.class);
            Root<RaDefendMeasures> root = cq.from(RaDefendMeasures.class);
            cq.select(root);
            TypedQuery<RaDefendMeasures> query = oldEntityManager.createQuery(cq);
            List<RaDefendMeasures> measuresList = query.getResultList();

            rowsProcessed = measuresList.size();

            // 获取所有defendCode列表，用于批量查询设置
            List<String> defendCodes = measuresList.stream()
                    .map(RaDefendMeasures::getDefendCode)
                    .filter(code -> code != null && !code.isEmpty())
                    .collect(Collectors.toList());

            // 第二步：查询所有相关的RaDefendMeasureSetting数据
            Map<String, List<RaDefendMeasureSetting>> settingsMap = new HashMap<>();
            if (!defendCodes.isEmpty()) {
                // 分批查询，避免IN子句过长
                int batchSize = 500;
                for (int i = 0; i < defendCodes.size(); i += batchSize) {
                    int endIndex = Math.min(i + batchSize, defendCodes.size());
                    List<String> batchCodes = defendCodes.subList(i, endIndex);

                    CriteriaBuilder settingsCb = oldEntityManager.getCriteriaBuilder();
                    CriteriaQuery<RaDefendMeasureSetting> settingsCq = settingsCb.createQuery(RaDefendMeasureSetting.class);
                    Root<RaDefendMeasureSetting> settingsRoot = settingsCq.from(RaDefendMeasureSetting.class);
                    settingsCq.select(settingsRoot);

                    // 构造复合条件
                    Predicate codeInBatch = settingsRoot.get("measureCode").in(batchCodes);
                    Predicate alarmTypeNotZero = settingsCb.notEqual(settingsRoot.get("alarmType"), 0);
                    Predicate alarmValueNotNull = settingsCb.isNotNull(settingsRoot.get("alarmValue"));
                    // 使用 and 组合多个查询条件
                    settingsCq.where(settingsCb.and(codeInBatch, alarmTypeNotZero, alarmValueNotNull));

                    List<RaDefendMeasureSetting> settingsList = oldEntityManager.createQuery(settingsCq).getResultList();

                    // 按measureCode分组
                    for (RaDefendMeasureSetting setting : settingsList) {
                        String measureCode = setting.getMeasureCode();
                        if (measureCode != null && !measureCode.isEmpty()) {
                            if (!settingsMap.containsKey(measureCode)) {
                                settingsMap.put(measureCode, new ArrayList<>());
                            }
                            settingsMap.get(measureCode).add(setting);
                        }
                    }
                }
            }

            // 第三步：在内存中组装数据并批量插入
            List<RiskProtection> riskProtections = new ArrayList<>();
            List<RiskProtectionAlertSetting> riskProtectionAlertSettings = new ArrayList<>();

            for (RaDefendMeasures measure : measuresList) {
                // 创建新的保护措施对象
                RiskProtection protection = new RiskProtection();

                // 设置基本字段
                protection.setId(measure.getId());
                protection.setTenantId(1L);
                protection.setDepartmentId(CommonUtils.toLong(measure.getTenantId())); // department_id使用tenant_id
                protection.setTagNumber(measure.getBitNum());
                protection.setProtectionDesc(CommonUtils.truncateIfTooLong(measure.getDefendDesc(), 255));
                protection.setPfd(CommonUtils.getValueOrDefault(measure.getPfd(), new BigDecimal("0.001")));
                protection.setDeviceId(measure.getNodeId());
                protection.setMeasureType(CommonUtils.toInteger(measure.getDefendType()));

                // 从typeJson中提取字段 (实际环境中需要使用JSON库)
                String typeJson = measure.getTypeJson();
                protection.setMainParameters(JsonUtils.getStringValue(typeJson, "$.majorParams"));
                protection.setMainFunction(JsonUtils.getStringValue(typeJson, "$.majorFunction"));
                protection.setSilLevel(JsonUtils.getStringValue(typeJson, "$.loopGrade"));
                protection.setSetValue(JsonUtils.getStringValue(typeJson, "$.setPoint"));
                protection.setSensorId(JsonUtils.getStringValue(typeJson, "$.sensorNum"));
                protection.setSensorRedundancy(JsonUtils.getStringValue(typeJson, "$.sensorRedundancy"));
                protection.setActuatorId(JsonUtils.getStringValue(typeJson, "$.actuatorNum"));
                protection.setActuatorRedundancy(JsonUtils.getStringValue(typeJson, "$.actuatorRedundancy"));
                protection.setInstruction(JsonUtils.getStringValue(typeJson, "$.disposeDirect"));
                protection.setUnit(JsonUtils.getStringValue(typeJson, "$.numericalUnit"));
                protection.setSetPressure(JsonUtils.getStringValue(typeJson, "$.setPressure"));
                protection.setDesignCondition(JsonUtils.getStringValue(typeJson, "$.designCondition"));
                protection.setReliefCapacity(JsonUtils.getStringValue(typeJson, "$.tappingAmount"));
                protection.setReliefDiameter(JsonUtils.getStringValue(typeJson, "$.tappingCalibre"));
                protection.setMaterialName(JsonUtils.getStringValue(typeJson, "$.materialName"));
                protection.setMaterialState(JsonUtils.getStringValue(typeJson, "$.materialStatus"));
                protection.setCapacity(JsonUtils.getStringValue(typeJson, "$.capacity"));

                // 设置警报阈值相关字段
                Byte alertType = 0;
                BigDecimal highLimit = null;
                BigDecimal highHighLimit = null;
                BigDecimal highHighHighLimit = null;
                BigDecimal lowLimit = null;
                BigDecimal lowLowLimit = null;
                BigDecimal lowLowLowLimit = null;

                // 处理防护措施设置
                String defendCode = measure.getDefendCode();
                if (defendCode != null && !defendCode.isEmpty() && settingsMap.containsKey(defendCode)) {
                    List<RaDefendMeasureSetting> settings = settingsMap.get(defendCode);
                    boolean hasHighSettings = false;
                    boolean hasLowSettings = false;

                    // 遍历设置记录
                    for (RaDefendMeasureSetting setting : settings) {
                        Byte alarmType = setting.getAlarmType();
                        String alarmValue = setting.getAlarmValue();

                        if (alarmType != null && alarmValue != null && !alarmValue.trim().isEmpty()) {
                            BigDecimal alarmValueDecimal = new BigDecimal(alarmValue);

                            // 根据alarm_type映射到对应的字段
                            switch (alarmType) {
                                case 1: // 高限
                                    highLimit = alarmValueDecimal;
                                    hasHighSettings = true;
                                    break;
                                case 2: // 高高限
                                    highHighLimit = alarmValueDecimal;
                                    hasHighSettings = true;
                                    break;
                                case 3: // 高高高限
                                    highHighHighLimit = alarmValueDecimal;
                                    hasHighSettings = true;
                                    break;
                                case 4: // 低限
                                    lowLimit = alarmValueDecimal;
                                    hasLowSettings = true;
                                    break;
                                case 5: // 低低限
                                    lowLowLimit = alarmValueDecimal;
                                    hasLowSettings = true;
                                    break;
                                case 6: // 低低低限
                                    lowLowLowLimit = alarmValueDecimal;
                                    hasLowSettings = true;
                                    break;
                            }
                        }
                    }

                    // 根据是否有高限或低限设置来确定alert_type
                    if (hasHighSettings && hasLowSettings) {
                        alertType = 0; // 全部
                    } else if (hasHighSettings) {
                        alertType = 1; // 高限预警
                    } else if (hasLowSettings) {
                        alertType = 2; // 低限预警
                    }
                }

                // 设置警报阈值字段
                protection.setAlertType(alertType);
                protection.setHighLimit(highLimit);
                protection.setHighHighLimit(highHighLimit);
                protection.setHighHighHighLimit(highHighHighLimit);
                protection.setLowLimit(lowLimit);
                protection.setLowLowLimit(lowLowLimit);
                protection.setLowLowLowLimit(lowLowLowLimit);

                // 设置其他字段
                protection.setAccidentChainsStatus(CommonUtils.toBoolean(measure.getLinkedFaultChains()));
                protection.setAccidentChainsNum(0);
                protection.setAlarmTagStatus(CommonUtils.toBoolean(measure.getLinkedAlarm()));
                protection.setSortNum(measure.getSortNo());
                protection.setCreateTime(CommonUtils.formatDateTime(measure.getCreateTime()));
                protection.setCreateBy(1L);
                protection.setUpdateTime(CommonUtils.formatDateTime(measure.getUpdateTime()));
                protection.setUpdateBy(1L);
                protection.setIsDel(measure.getIsDel());

                // 添加到列表中
                riskProtections.add(protection);

                // 插入多级预警阈值
                RiskProtectionAlertSetting alertSetting = new RiskProtectionAlertSetting();
                alertSetting.setId(protection.getId().intValue());
                alertSetting.setProtectionId(protection.getId());
                Byte alertTypeValue = protection.getAlertType() != null ? protection.getAlertType() : 0;
                alertSetting.setAlertType(alertTypeValue);
                alertSetting.setHighLimit(protection.getHighLimit());
                alertSetting.setHighHighLimit(protection.getHighHighLimit());
                alertSetting.setHighHighHighLimit(protection.getHighHighHighLimit());
                alertSetting.setLowLimit(protection.getLowLimit());
                alertSetting.setLowLowLimit(protection.getLowLowLimit());
                alertSetting.setLowLowLowLimit(protection.getLowLowLowLimit());
                riskProtectionAlertSettings.add(alertSetting);
            }

            // 批量保存到新数据库
            int batchSize = 100;
            for (int i = 0; i < riskProtections.size(); i += batchSize) {
                int endIndex = Math.min(i + batchSize, riskProtections.size());
                List<RiskProtection> batch = riskProtections.subList(i, endIndex);

                // 批量插入
                for (RiskProtection protection : batch) {
                    newEntityManager.persist(protection);
                }

                // 每批次提交一次，清除缓存
                newEntityManager.flush();
                newEntityManager.clear();
            }

            if(!riskProtectionAlertSettings.isEmpty()){
                int settingBatchSize = 100;
                for (int i = 0; i < riskProtectionAlertSettings.size(); i += settingBatchSize) {
                    int endIndex = Math.min(i + settingBatchSize, riskProtectionAlertSettings.size());
                    List<RiskProtectionAlertSetting> settingBatch = riskProtectionAlertSettings.subList(i, endIndex);

                    // 批量插入
                    for (RiskProtectionAlertSetting alertSetting : settingBatch) {
                        newEntityManager.merge(alertSetting);
                    }

                    // 每批次提交一次，清除缓存
                    newEntityManager.flush();
                    newEntityManager.clear();
                }
            }

            // 提交事务
            newEntityManager.getTransaction().commit();
            oldEntityManager.getTransaction().commit();

            return rowsProcessed;

        } catch (Exception e) {
            // 发生异常，回滚事务
            if (newEntityManager.getTransaction().isActive()) {
                newEntityManager.getTransaction().rollback();
            }
            if (oldEntityManager.getTransaction().isActive()) {
                oldEntityManager.getTransaction().rollback();
            }
            throw new RuntimeException("迁移设备-保护措施数据失败", e);
        } finally {
            // 关闭实体管理器
            oldEntityManager.close();
            newEntityManager.close();
        }
    }

    /**
     * 生成迁移数据的SQL
     * 由于使用了自定义内存处理，这里返回一个伪SQL仅供记录
     * 实际迁移逻辑在executeCustomMigration方法中
     *
     * @return 迁移数据的SQL
     */
    @Override
    protected String generateMigrationSql() {
        return "SELECT 1 FROM dual WHERE 1=0";  // 返回一个不进行实际操作的SQL
    }

    /**
     * 获取源数据SQL
     *
     * @return a description of SQL
     */
    public String getSourceSql() {
        return "-- 使用JPA实体类查询，而非直接SQL\n" +
               "-- RaDefendMeasures实体对应ra_defend_measures表\n" +
               "-- RaDefendMeasureSetting实体对应ra_defend_measure_setting表\n\n" +
               "-- 关联关系：\n" +
               "-- RaDefendMeasures.defendCode = RaDefendMeasureSetting.measureCode\n\n" +
               "-- 其中，alarm_type映射关系如下：\n" +
               "-- alarm_type=1 → high_limit (高限)\n" +
               "-- alarm_type=2 → high_high_limit (高高限)\n" +
               "-- alarm_type=3 → high_high_high_limit (高高高限)\n" +
               "-- alarm_type=4 → low_limit (低限)\n" +
               "-- alarm_type=5 → low_low_limit (低低限)\n" +
               "-- alarm_type=6 → low_low_low_limit (低低低限)";
    }

    /**
     * 获取目标数据SQL模板
     *
     * @return 目标数据SQL模板
     */
    public String getTargetSqlTemplate() {
        return "-- 使用JPA实体类保存，而非直接SQL\n" +
               "-- RiskProtection实体对应risk_protection表";
    }
}
